
clear all
set more off
insheet using "Ad report.csv"

collapse (sum) impressions clicks, by(adname)

save "collapsed.dta", replace

clear all

insheet using "condinfo.csv"

save "condinfo.dta", replace

clear all

use "collapsed.dta"

merge 1:1 adname using "condinfo.dta", keepus(title body link regions relationship gender ///
agemin broadcategoryclusters image)

drop _merge

gen conda = 0
gen condb = 0
gen condc = 0
gen condd = 0
gen conde = 0
gen condf = 0
gen condg = 0
gen condh = 0
gen condi = 0
gen condj = 0
gen condk = 0
gen condl = 0

replace conda = 1 if regexm(link, "coa")
replace condb = 1 if regexm(link, "cob")
replace condc = 1 if regexm(link, "coc")
replace condd = 1 if regexm(link, "cod")
replace conde = 1 if regexm(link, "coe")
replace condf = 1 if regexm(link, "cof")
replace condg = 1 if regexm(link, "cog")
replace condh = 1 if regexm(link, "coh")
replace condi = 1 if regexm(link, "coi")
replace condj = 1 if regexm(link, "coj")
replace condk = 1 if regexm(link, "cok")
replace condl = 1 if regexm(link, "col")

gen cond = ""
replace cond = "a" if conda==1
replace cond = "b" if condb==1
replace cond = "c" if condc==1
replace cond = "d" if condd==1
replace cond = "e" if conde==1
replace cond = "f" if condf==1
replace cond = "g" if condg==1
replace cond = "h" if condh==1
replace cond = "i" if condi==1
replace cond = "j" if condj==1
replace cond = "k" if condk==1
replace cond = "l" if condl==1

* Factor variables
gen gaffe=0
replace gaffe = 1 if regexm(title, "Oops")

gen romneyneg = 0
replace romneyneg = 1 if regexm(body, "Romney")

gen lib = 0
replace lib = 1 if regexm(broadcategoryclusters, "Liberal")

gen cons = 0
replace cons = 1 if regexm(broadcategoryclusters, "Conservative")

gen partisan = 0
replace partisan = 1 if lib==1 | cons==1

gen sports = 0
replace sports = 1 if regexm(broadcategoryclusters, "Music")

gen music = 0
replace music = 1 if regexm(broadcategoryclusters, "Sports")

gen ind = 0
replace ind = 1 if sports==1 | music==1

gen ctp = (clicks / impressions) * 100 // click-through percentage
gen ctpr = ctp * 100  // Rescaled click-through percentage

gen geog = 0
replace geog = 1 if regexm(regions, "Texas")

gen female = 0
replace female = 1 if regexm(gender, "Women")

gen relat = 0
replace relat = 1 if regexm(relationship, "Single")

rename agemin age

gen age2 = (age - 18) / 46 // scale 0-1

gen ownad = .

replace ownad = 1 if cond=="c" | cond=="d" | cond=="e" | cond=="f"
replace ownad = 0 if cond=="a" | cond=="b" | cond=="g" | cond=="h"

* Restructuring of DV for one table in the SI.
gen ctpr2 = ctpr
replace ctpr2 = . if ctpr<=0
gen ctpr3 = ctpr
replace ctpr3 = 0 if ctpr<=0

save "working.dta", replace
